package com.rongji.cms.tools.temp.db.utils;

import com.rongji.cms.tools.temp.db.entity.DbInfo;
import com.rongji.cms.tools.temp.db.enums.DbEnum;

import java.lang.reflect.Method;
import java.sql.*;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @author yang
 * @date 2023/1/29 9:03
 * @description
 */

public class DbUtil {
    //    mysql
    public final static String MYSQL_DRIVER = "com.mysql.cj.jdbc.Driver";
    public final static String MYSQL_TABLE_NAME = "show tables";
    // oracle
    public final static String ORACLE_DRIVER = "oracle.jdbc.driver.OracleDriver";
    public final static String ORACLE_TABLE_NAME = " select table_name  from tabs  ";


    private static Connection getConnection(String dbType, DbInfo dbInfo) {
        Connection conn = null;
        String url = null;

        String dbHost = dbInfo.getDbHost();
        String dbName = dbInfo.getDbName();
        String dbUsername = dbInfo.getDbUsername();
        String dbPassword = dbInfo.getDbPassword();
        url = getConnectUrl(dbType, dbHost, dbName);
        InsDriver(dbType);
        try {
            conn = DriverManager.getConnection(url, dbUsername, dbPassword);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return conn;
    }

    private static void InsDriver(String dbType) {
        String driverName = null;
        if (dbType.equals(DbEnum.MYSQL.getValue())) {
            driverName = MYSQL_DRIVER;
        } else if (dbType.equals(DbEnum.ORACLE.getValue())) {
            driverName = ORACLE_DRIVER;
        }
        try {
            Class.forName(driverName).newInstance();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    private static ResultSet getTableResultSet(String dbType, Statement tableStatement) {
        ResultSet tableResultSet = null;
        try {
            if (dbType.equals(DbEnum.MYSQL.getValue())) {
                tableResultSet = tableStatement.executeQuery(MYSQL_TABLE_NAME);
            } else if (dbType.equals(DbEnum.ORACLE.getValue())) {
                tableResultSet = tableStatement.executeQuery(ORACLE_TABLE_NAME);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tableResultSet;
    }


    private static String getConnectUrl(String dbType, String host, String dbName) {
        StringBuilder sb = new StringBuilder();
        if (dbType.equals(DbEnum.MYSQL.getValue())) {
            sb.append("jdbc:mysql://")
                    .append(host)
                    .append("/")
                    .append(dbName)
                    .append("?useUnicode=true&useSSL=false&characterEncoding=utf8");
        } else if (dbType.equals(DbEnum.ORACLE.getValue())) {
            sb.append("jdbc:oracle:thin:@")
                    .append(host)
                    .append(":")
                    .append(dbName);
        }


        return sb.toString();
    }


    private static String getColumnsSQL(String dbType, String dbName, String tableName) {
        StringBuilder sb = new StringBuilder();

        if (dbType.equals(DbEnum.MYSQL.getValue())) {
            sb.append("select * from information_schema.COLUMNS where TABLE_SCHEMA = '")
                    .append(dbName)
                    .append("' and TABLE_NAME = '")
                    .append(tableName)
                    .append("'");
        } else if (dbType.equals(DbEnum.ORACLE.getValue())) {
            //        select * from user_tab_cols where table_name='BACK_LOG';
//        user_tab_columns更合适
            sb.append("select * from user_tab_columns  ")
                    .append(" where TABLE_NAME = '")
                    .append(tableName)
                    .append("'");
        }


        return sb.toString();
    }


    public static <T> Map<String, Map<String, T>> getDBInfo(String dbType, DbInfo dbInfo, Class<T> clazz) throws Exception {
        Map<String, Map<String, T>> tableMap = new ConcurrentHashMap<>();
        Connection conn = null;
        Statement tableStatement = null;
        ResultSet tableResultSet = null;
        String dbName = dbInfo.getDbName();
        try {
            conn = getConnection(dbType, dbInfo);
            tableStatement = conn.createStatement();
            tableResultSet = getTableResultSet(dbType, tableStatement);

            int tableColumnCount = tableResultSet.getMetaData().getColumnCount();
            while (tableResultSet.next()) {
                for (int i = 1; i <= tableColumnCount; i++) {
                    String tableName = tableResultSet.getString(i);
                    ResultSet columnResultSet = null;
                    Statement columnStatement = null;
                    try {
                        columnStatement = conn.createStatement();
                        columnResultSet = columnStatement.executeQuery(getColumnsSQL(dbType, dbName, tableName));

                        Method method = clazz.getMethod("getFromResult", ResultSet.class);
//                        静态方法，第一个参数为null
                        Map<String, T> columnsMap = (Map<String, T>) method.invoke(null, columnResultSet);


                        tableMap.put(tableName, columnsMap);
                    } finally {
                        closeConnection(columnResultSet, columnStatement, null);
                    }
                }
            }
        } finally {
            closeConnection(tableResultSet, tableStatement, conn);
        }
        return tableMap;
    }


    private static void closeConnection(ResultSet rSet, Statement psment, Connection conn) throws Exception {
        try {
            if (rSet != null) {
                rSet.close();
                rSet = null;
            }
            if (psment != null) {
                psment.close();
                psment = null;
            }
            if (conn != null && !conn.isClosed()) {
                conn.close();
                conn = null;
            }
        } catch (SQLException e) {
            throw new Exception("Fail to close ResultSet / PreParedStatment / Connection !");
        }
    }
}
